package core.api;

import jade.util.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;

import core.api.performance.PerformanceDescription;
import core.database.StatementSelect;
import core.database.StatementUpdate;
import core.datamodel.Constants;
import core.datamodel.Element;
import core.datamodel.ResourceCounter;
import core.datamodel.ElementValue;
import core.datamodel.Event;
import core.datamodel.Property;
import core.datamodel.Resource;

public class DbData {
	private Logger log = Logger.getJADELogger(this.getClass().getName());
	private Connection conDb = null;
	private PreparedStatement allResource; // per metodo getAllResourceAndElement
	
	public DbData (Connection conDb){
		this.conDb= conDb;
		
		// TODO-fix max cursor ... rifare tutto. metodo getAllResourceAndElement
		String queryAllResourceActive="select rsc.*, ele.*, ele.ID as ELEMENT_ID from customers_resource rsc, resource_elements ele " +
		"where rsc.id=ele.customer_resource_id and rsc.resource_status=? " +
		"order by ele.customer_resource_id";
		//preparo gli statement
		try {
			allResource = conDb.prepareStatement(queryAllResourceActive);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void  update (String table, Hashtable<String,String> keyValues, Long idRecord ) throws SQLException{

		String query = "update "+table+" ";
		String field ="";
		String value ="";
		boolean first = true;
		for (Enumeration e =keyValues.keys(); e.hasMoreElements(); ){
			field = (String) e.nextElement(); 
			value = (String) keyValues.get(field);
			if (first) {
				query = query + "set "+field+"='"+value+"'";
				first = false;
			} else
				query = query + ", "+field+"='"+value+"'";
		}
		query = query + "where id=" + idRecord;
		log.info("update query: " + query );
		PreparedStatement update = conDb.prepareStatement(query);
		update.execute();

		update.close();

	}


	public Resource getResource(String customerName, String serviceName, String resourceName ) throws SQLException{

		PreparedStatement getResource;
		String queryResource = "select rsc.*, ele.*, ele.ID as ELEMENT_ID from CUSTOMERS_RESOURCE rsc, resource_elements ele " +
		"where ele.customer_resource_id=rsc.id " +
		"and customer=? and resource_name=? and service=?";

		getResource=conDb.prepareStatement(queryResource);
		getResource.setString(1,customerName);
		getResource.setString(2,resourceName);
		getResource.setString(3,serviceName);
		ResultSet alldata = getResource.executeQuery();

		Resource rsc=null;
		ArrayList<Element> elements = null;
		boolean setResource = false;

		while (alldata.next()) {

			if (!setResource){
				rsc = new Resource();
				rsc.setId(alldata.getLong("ID"));

				rsc.setCustomer(alldata.getString("CUSTOMER"));
				rsc.setService(alldata.getString("SERVICE"));
				rsc.setExternalCustomerId(alldata.getString("EXTERNAL_CUSTOMER_ID"));

				rsc.setResourceName(alldata.getString("RESOURCE_NAME"));
				rsc.setResourceType(alldata.getString("RESOURCE_TYPE"));
				rsc.setOffer(alldata.getString("RESOURCE_OFFER"));

				rsc.setExternalResourceId(alldata.getLong("EXTERNAL_RESOURCE_ID"));
				rsc.setStatus(alldata.getString("RESOURCE_STATUS"));
				elements =   new ArrayList<Element> ();
				rsc.setElements(elements);

				setResource = true;
			}


			// aggiungo gli elementi alla risorsa esistente



			Element ele = new Element();

			ele.setId(alldata.getLong("ELEMENT_ID"));
			ele.setCustomerResourceId(alldata.getLong("CUSTOMER_RESOURCE_ID"));
			ele.setExternalId(alldata.getLong("ELEMENT_EXTERNAL_ID"));

			ele.setName(alldata.getString("ELEMENT_NAME"));
			ele.setType(alldata.getString("ELEMENT_TYPE"));
			ele.setValue(alldata.getString("ELEMENT_VALUE"));

			ele.setPerformanceDescriptionId(alldata.getLong("PERFORMANCE_DESCRIPTION_ID"));
			ele.setNeedPerformanceData(alldata.getBoolean("NEED_PERFORMANCE_DATA"));

			elements.add(ele);

		} // fine ciclo risorse
		rsc.setElements(elements);
		System.out.println(rsc.getCustomer()+ " " + rsc.getResourceName() + " " + rsc.getId());

		alldata.close();
		getResource.close();

		return rsc;
	}



	public long insertResource(Resource rsc) throws SQLException{


		PreparedStatement insertResource,getId, insertElement,getElementId;

		String resourceQuery=" insert into CUSTOMERS_RESOURCE " +
		"(ID,CUSTOMER, SERVICE, EXTERNAL_CUSTOMER_ID,RESOURCE_NAME, RESOURCE_TYPE, RESOURCE_OFFER,EXTERNAL_RESOURCE_ID, RESOURCE_STATUS) " +
		"VALUES " +
		"(?, ? , ?, ?, ? ,?, ? ,?,? )";

		String newIdQuery ="select customer_resource_id_seq.nextval from dual";


		String elementQuery="insert into RESOURCE_ELEMENTS " +
		"(ID, CUSTOMER_RESOURCE_ID, ELEMENT_EXTERNAL_ID,ELEMENT_NAME, ELEMENT_VALUE, ELEMENT_TYPE, PERFORMANCE_DESCRIPTION_ID,NEED_PERFORMANCE_DATA ) " +
		"VALUES ( resource_elements_id_seq.nextval, ?, ?, ?, ?, ?, ?,?)";



		//preparo gli statement
		getId = conDb.prepareStatement(newIdQuery);
		insertResource = conDb.prepareStatement(resourceQuery);
		insertElement = conDb.prepareStatement(elementQuery);

		// mi prendo il nuovo id sequenza
		ResultSet rs = getId.executeQuery();
		rs.next();
		long newId =  rs.getLong("NEXTVAL");


		// inserisco la risorsa
		insertResource.setLong(1,newId);
		insertResource.setString(2,rsc.getCustomer());
		insertResource.setString(3,rsc.getService());
		insertResource.setString(4,rsc.getExternalCustomerId());

		insertResource.setString(5,rsc.getResourceName());
		insertResource.setString(6,rsc.getResourceType());
		insertResource.setString(7,rsc.getOffer());
		insertResource.setLong(8,rsc.getExternalResourceId());
		insertResource.setString(9,Constants.RESOURCE_STATUS_ACTIVE);

		insertResource.execute();

		// inserisco tutti i record di element
		ArrayList <Element> elements = rsc.getElements();
		for (Element  element : elements){

			insertElement.setLong(1,newId);
			insertElement.setLong(2,element.getExternalId());
			insertElement.setString(3, element.getName());
			insertElement.setString(4, element.getValue());
			insertElement.setString(5, element.getType());
			insertElement.setLong(6,element.getPerformanceDescription().getId());
			insertElement.setBoolean(7,element.isNeedPerformanceData());
			insertElement.execute();
		}

		insertResource.close();
		insertElement.close();

		return newId;
	}

	public void deleteResource (Resource rsc) throws SQLException{
		PreparedStatement deleteResource, deleteElement;

		String resourceQuery = "delete from CUSTOMERS_RESOURCE where id=?";
		String elementQuery = "delete from RESOURCE_ELEMENTS where customer_resource_id=?";

		long delId = rsc.getId();


		deleteResource = conDb.prepareStatement(resourceQuery);
		deleteElement = conDb.prepareStatement(elementQuery);

		deleteResource.setLong(1,delId);
		deleteResource.execute();

		deleteElement.setLong(1,delId);
		deleteElement.execute();

		deleteResource.close();
		deleteElement.close();

	}

	public long insertEvent(Event event)throws SQLException{

		PreparedStatement insertEvent,getId, insertProperty,getPropertyId;

		String eventQuery=" insert into EVENTS " +
		"(ID,CUSTOMERS_RESOURCE_ID, EXTERNAL_EVENT_ID, EVENT_TIME, EVENT_ACTION, EVENT_PRIORITY, EVENT_DESCRIPTION) " +
		"VALUES " +
		"(?, ? , ?, ?, ? ,?, ?  )";

		String eventIdQuery ="select events_id_seq.NEXTVAL from dual";

		String propertyQuery="insert into EVENT_PROPERTIES " +
		"(ID, EVENT_ID, NAME, TYPE, NEW_VALUE, LAST_VALUE ) " +
		"VALUES ( event_properties_id_seq.NEXTVAL, ?, ?, ?, ?, ?)";

		//preparo gli statement
		getId = conDb.prepareStatement(eventIdQuery);

		insertEvent = conDb.prepareStatement(eventQuery);
		insertProperty = conDb.prepareStatement(propertyQuery);


		// mi prendo il nuovo id sequenza
		ResultSet rs = getId.executeQuery();
		rs.next();
		long newId =  rs.getLong("NEXTVAL");


		// inserisco event
		insertEvent.setLong(1,newId);
		insertEvent.setLong(2,event.getEventResource().getId());
		insertEvent.setLong(3,event.getExternalEventId());

		insertEvent.setTimestamp(4, new Timestamp( System.currentTimeMillis()));
		insertEvent.setString(5, event.getAction());
		insertEvent.setString(6, event.getPriority());
		insertEvent.setString(7, event.getDescription());

		insertEvent.execute();

		// inserisco le property
		ArrayList<Property> properties = event.getProperties();
		if( properties != null) {
			for (Property pro : properties ){

				insertProperty.setLong(1,newId);
				insertProperty.setString(2,pro.getName() );
				insertProperty.setString(3, pro.getType());
				insertProperty.setString(4, pro.getNewValue());
				insertProperty.setString(5, pro.getLastValue());

				insertProperty.execute();
			}
		}

		rs.close();
		insertEvent.close();
		getId.close();
		insertProperty.close();
		return newId;

	}

	public void deleteEvent (Event event) throws SQLException{
		PreparedStatement deleteEvent, deleteProperty;

		String deleteEventQuery = "delete from EVENTS where id=?";
		String deletePropertyQuery = "delete from EVENT_PROPERTIES where event_id=?";

		long delId = event.getId();


		deleteEvent = conDb.prepareStatement(deleteEventQuery);
		deleteProperty = conDb.prepareStatement(deleteEventQuery);

		deleteEvent.setLong(1,delId);
		deleteEvent.execute();

		deleteProperty.setLong(1,delId);
		deleteProperty.execute();

		deleteEvent.close();
		deleteProperty.close();
	}

	public void insertElementValue (ArrayList <ElementValue> eValues) throws SQLException {
		if (eValues != null){
		PreparedStatement insertValue;

		String query=" insert into ELEMENT_VALUES " +
		"(ID, ELEMENT_ID, TIMEVALUE, USAGE, LIMIT_USAGE, TOTAL_DAY_USAGE, CHARGE, LIMIT_CHARGE, TOTAL_DAY_CHARGE) " +
		"VALUES " +
		"( element_values_id_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ? )";

		//preparo gli statement
		insertValue = conDb.prepareStatement(query);

		for (ElementValue ele : eValues) {
			System.out.println("Inizio costruzione insert account  con element id="+ele.getElementId());
			System.out.println("charge="+ele.getCharge());
			System.out.println("limit charge="+ele.getLimitCharge());
			System.out.println("total day charge="+ele.getTotalDayCharge());
			
			insertValue.setLong(1, ele.getElementId());
			insertValue.setTimestamp(2,new Timestamp( ele.getTimestamp().getTime()));

			insertValue.setFloat(3,ele.getUsage());
			insertValue.setFloat(4,ele.getLimitUsage());
			insertValue.setFloat(5,ele.getTotalDayUsage());

			insertValue.setFloat(6,ele.getCharge());
			insertValue.setFloat(7,ele.getLimitCharge());
			insertValue.setFloat(8,ele.getTotalDayCharge());
			
			insertValue.execute();

		}

		insertValue.close();
		} else {
			log.info("No element values in account data");
		}
	}

	public ResultSet getAllResourceAndElement (String status)throws SQLException {


		allResource.setString(1, status);

		return allResource.executeQuery();


	}

	public ArrayList<Event> getResourceEvents(Resource rsc, Date start, long interval) throws SQLException{

		PreparedStatement allEvents, allProperty;

		String queryAllEvents="select id,event_time, event_action, event_priority, event_description " +
		"from events " +
		"where customers_resource_id=? and " +
		"event_time>=? and event_time<?";

		String queryAllProperty = "select id, name, type, new_value, last_value " +
		"from event_properties " +
		"where event_id=?";

		//preparo gli statement
		allEvents = conDb.prepareStatement(queryAllEvents);
		allProperty = conDb.prepareStatement(queryAllProperty);

		allEvents.setLong(1, rsc.getId());
		allEvents.setTimestamp(2, new Timestamp(start.getTime()));
		allEvents.setTimestamp(3, new Timestamp(start.getTime()+ interval));


		ResultSet rs = allEvents.executeQuery();
		ArrayList<Event> events = new ArrayList<Event> ();

		while (rs.next()) {
			Event event = new Event();
			event.setId(rs.getLong("ID"));
			event.setAction(rs.getString("EVENT_ACTION"));
			event.setPriority(rs.getString("EVENT_PRIORITY"));
			event.setDescription(rs.getString("EVENT_DESCRIPTION"));
			event.setEventTime(rs.getTimestamp("EVENT_TIME"));

			event.setResourceId(rsc.getId());

			// recupero le property

			allProperty.setLong(1,event.getId());
			ResultSet prs = allProperty.executeQuery();

			ArrayList<Property> properties = new ArrayList<Property>();
			while (prs.next()) {

				Property pro = new Property();
				pro.setEventId(event.getId());
				pro.setId(prs.getLong("ID"));
				pro.setName(prs.getString("NAME"));
				pro.setType(prs.getString("TYPE"));
				pro.setNewValue(prs.getString("NEW_VALUE"));
				pro.setNewValue(prs.getString("LAST_VALUE"));

				properties.add(pro);
			}
			prs.close();
			event.setProperties(properties);

			events.add(event);
		}

		rs.close();
		allEvents.close();
		allProperty.close();

		return events;

	}

	public PerformanceDescription getPerformanceDescription (Resource resource, Element  element)throws SQLException{


		PerformanceDescription pd = null;

		PreparedStatement getPerfDescr;
		String query="select * from PERFORMANCE_DESCRIPTION " +
		" where resource_type=? and element_name=? " ;

		getPerfDescr = conDb.prepareStatement(query);
		getPerfDescr.setString(1, resource.getResourceType());
		getPerfDescr.setString(2, element.getName());

		ResultSet rspd = getPerfDescr.executeQuery();

		if (rspd.next()){

			pd = new PerformanceDescription();
			pd.setId(rspd.getLong("ID"));
			pd.setResourceType(rspd.getString("RESOURCE_TYPE"));
			pd.setElementName(rspd.getString("ELEMENT_NAME"));

			pd.setPerformanceElement(rspd.getBoolean("PERFORMANCE_ELEMENT"));
			pd.setPerformanceElementName(rspd.getString("PERFORMANCE_ELEMENT_NAME"));
			pd.setPerformanceCategoryName(rspd.getString("PERFORMANCE_CATEGORY_NAME"));

			pd.setGuiElement(rspd.getBoolean("GUI_ELEMENT"));
			pd.setGuiElementVisible(rspd.getBoolean("GUI_ELEMENT_VISIBLE"));

			log.info("Performance Descriptor con id: "+ pd.getId()+" trovato per :" + resource.getResourceType()+" - "+element.getName());

		} else {
			// assegno perfDescriptor di default
			pd = new PerformanceDescription();
			// set default performance descriptor
			pd.setId(1);

			log.info("Nessun performance Descriptor trovato per :" + resource.getResourceType()+" - "+element.getName());
		}
		rspd.close();
		getPerfDescr.close();

		return pd;
	}

	public ArrayList<ResourceCounter>  getCounters(long id){
		ArrayList<ResourceCounter> counters = null;

		StatementSelect query = new StatementSelect(this.conDb );
		query.setTableName("RESOURCE_COUNTERS");
		query.addField("*");
		query.addWhere("resource_id", id);
		boolean first = true;
		try {
			ResultSet rs = query.executeSelect();
			while (rs.next()){
				ResourceCounter counter = new ResourceCounter();
				counter.setResourceId(rs.getLong("RESOURCE_ID"));
				counter.setCounterName(rs.getString("COUNTER_KEYNAME"));
				counter.setLastUpdate(rs.getTimestamp("LAST_UPDATE"));
				counter.setCounterValue(rs.getFloat("COUNTER_VALUE"));

				if (first) {
					counters = new ArrayList<ResourceCounter>();
					first = false;
				}
				counters.add(counter);
			}
			query.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 		
		return counters;
	}

	public void updateAndInsertCounters(ArrayList<ResourceCounter> counters) {
		if (counters != null){
			String queryUpdate = " UPDATE resource_counters " +
			"  SET COUNTER_VALUE=? , LAST_UPDATE=? " +
			" where resource_id=? and counter_keyname=?";

			String queryInsert=" insert into resource_counters " +
			"(RESOURCE_ID,LAST_UPDATE, COUNTER_KEYNAME, COUNTER_VALUE) " +
			"VALUES " +
			"(?, ? , ?, ?)";


			try {
				PreparedStatement update = conDb.prepareStatement(queryUpdate);
				PreparedStatement insert = conDb.prepareStatement(queryInsert);

				for (ResourceCounter cntx : counters){
					if (cntx.isNewCounter()){
						insert.setLong(1,cntx.getResourceId());
						insert.setTimestamp(2,new Timestamp( cntx.getLastUpdate().getTime()));
						insert.setString(3, cntx.getCounterName());
						insert.setFloat(4,cntx.getCounterValue());
						insert.execute();

					} else if (cntx.isUpdateCounter()){
						update.setFloat(1,cntx.getCounterValue());
						update.setTimestamp(2,new Timestamp(cntx.getLastUpdate().getTime()));
						update.setLong(3,cntx.getResourceId());
						update.setString(4, cntx.getCounterName());
						update.execute();
					} 
				}
				update.close();
				insert.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else {
			log.info("No counters in account data");
		}

	}


}
